Limitations of Relationships Constructed from Coinciding Data

Partitioning of data with the group by clause is in SQL defined by one or more attributes from a single, possibly derived relation. The language therefore allows groupings defined by compositions of basic and/or referential attributes, i.e. foreign key attributes. However, partitioning applied to a view derived from more than one relation can lead to misinterpretation, ambiguity and/or incomplete results of which examples are shown. We argue that the specification of data aggregated from different tables must solely be based on referential attributes. Consequently, if such attributes are absent, the only way to partition the data reliably is to improve the underlying data model by adding structural aggregation. We illustrate this by simple examples where a semantic framework is used for database design and data manipulation.